package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by pjn on 2017/3/3.
 */
public class NoticeAddRepositoryImpl {
    @PersistenceContext
    private EntityManager em;


    public List<NoticeListDto> getNoticesended4DT(DTRequestDto dtRequestDto) throws Exception {
        String sql = "SELECT n.NoticeId AS noticeId,n.NoticeTitle AS noticeTitle,n.NoticeContent AS noticeContent,n.NoticeSendTime AS noticeSendTime\n" +
                " FROM tb_notice n\n" +
                " WHERE n.NoticeType=0";
        if (dtRequestDto.getSearch() != null) {
            sql += " AND n.NoticeTitle LIKE '%" + dtRequestDto.getSearch() + "%'";
        }
        sql += " ORDER BY n.NoticeSendTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(NoticeListDto.class));
        List<NoticeListDto> dtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return dtos;
    }

    public Integer getNoticesendedCount4DT(String search) throws Exception {
        String sql = "SELECT COUNT(*)\n" +
                " FROM tb_notice n\n" +
                " WHERE n.NoticeType=0";
        if (!search.equals("")) {
            sql += " AND n.NoticeTitle LIKE '%" + search + "%'";
        }
        sql += " ORDER BY n.NoticeSendTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class);
        Integer result = Integer.parseInt(query.getSingleResult().toString());
        return result;
    }

    public List<NoticeListDto> getNoticeAppSended4DT(String search, DTRequestDto dtRequestDto, Integer userId) throws Exception{
        String sql = "SELECT n.NoticeId AS noticeId,n.NoticeTitle AS noticeTitle,n.NoticeContent AS noticeContent," +
                "n.NoticeSendTime AS noticeSendTime,n.NoticePic AS noticePic" +
                " FROM tb_notice n\n" +
                " WHERE n.NoticeType=1";
        if (search != "" || search != null) {
            sql += " AND n.NoticeTitle LIKE '%" + search + "%'";
        }
        sql += " ORDER BY n.NoticeSendTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(NoticeListDto.class));
        List<NoticeListDto> dtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return dtos;
    }

    public Integer getNoticeAppSendedCount4DT(String search, Integer userId) throws Exception{
        String sql = "SELECT COUNT(*)\n" +
                " FROM tb_notice n\n" +
                " WHERE n.NoticeType=1";
        if (!search.equals("")) {
            sql += " AND n.NoticeTitle LIKE '%" + search + "%'";
        }
        sql += " ORDER BY n.NoticeSendTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class);
        Integer result = Integer.parseInt(query.getSingleResult().toString());
        return result;
    }


    public List<NoticeListDto> getAppPicNotice() throws Exception{
        String sql = "SELECT n.NoticeId AS noticeId,n.NoticeTitle AS noticeTitle,n.NoticeSendTime AS noticeSendTime,n.NoticeContent AS noticeContent,n.NoticePic AS noticePic\n" +
                "FROM tb_notice n\n" +
                "WHERE n.NoticeType=1\n" +
                "ORDER BY n.NoticeSendTime DESC\n" +
                "LIMIT 0,4";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(NoticeListDto.class));;
        return (List<NoticeListDto>)query.getResultList();
    }


}
